package com.logisticsmanagement.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.logisticsmanagement.domain.City;
import com.logisticsmanagement.domain.User;
import com.logisticsmanagement.utils.DataSource;
import com.logisticsmanagement.utils.DatabaseUtil;

public class UserDao {

	public User selectByID(String  id){
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet rs = null;
		User ret = null;

		try {
			conn = DataSource.getConnection();
			if(conn == null)
				return null;
			sta = conn.prepareStatement("select * from User where User =?");
			sta.setString(1, id);
			rs = sta.executeQuery();
			if(rs.next()){
				ret = new User();
				ret.setUserID(id);
				ret.setDeliveryStationID(rs.getInt("deliveryStationID"));
				ret.setRole(rs.getString("role"));
				ret.setDisplayName(rs.getString("displayName"));
				ret.setGender(rs.getInt("gender"));
				ret.setMobilePhone(rs.getString("mobilePhone"));
				ret.setAddress(rs.getString("address"));
				ret.setPrepareDelete(rs.getBoolean("prepareDelete"));
				ret.setIsDelete(rs.getBoolean("isDelete"));
				ret.setPassword(rs.getString("password"));
				ret.setLastLoginTime(rs.getDate("lastLoginTime"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(conn != null){
				try {
					rs.close();
					sta.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return ret;
	}

	public Boolean add(User user){
		
		Connection conn = null;
		PreparedStatement sta = null;

		try {
			conn = DataSource.getConnection();
			if(conn == null)
				return null;
			sta = conn.prepareStatement("insert into  User  ("
					+ "userID,deliveryStationID,role,displayName,gender,mobilePhone,address,prepareDelete,"
					+ "isDelete,password,lastLoginTime) values(?,?,?,?,?,?,?,?,?,?,?)");
			sta.setString(1, user.getUserID());
			sta.setInt(2, user.getDeliveryStationID());
			sta.setString(3, user.getRole());
			sta.setString(4, user.getDisplayName());
			sta.setInt(5, user.getGender());
			sta.setString(6, user.getMobilePhone());
			sta.setString(7, user.getAddress());
			sta.setBoolean(8, user.getPrepareDelete());
			sta.setString(9, user.getPassword());
			sta.setDate(10, new java.sql.Date(user.getLastLoginTime().getTime()));
			sta.setBoolean(11, user.getIsDelete());
			return (sta.executeUpdate()==1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(conn != null){
				try {
					sta.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return false;
	}
	
	
	public Boolean update(User user){
		Connection conn = null;
		PreparedStatement sta = null;

		try {
			conn = DataSource.getConnection();
			if(conn == null)
				return null;
			sta = conn.prepareStatement("update User set "
					+ "deliveryStatioolnID=? "
					+ "role=? "
					+ "displayName=? "
					+ "gender=? "
					+ "mobilePhone=? "
					+ "address=? "
					+ "prepareDelete=? "
					+ "isDelete=? "
					+ "password=? "
					+ "lastLoginTime=? "
					+ "where userID=?");
			sta.setInt(1, user.getDeliveryStationID());
			sta.setString(2, user.getRole());
			sta.setString(3, user.getDisplayName());
			sta.setInt(4, user.getGender());
			sta.setString(5, user.getMobilePhone());
			sta.setString(6, user.getAddress());
			sta.setBoolean(7, user.getPrepareDelete());
			sta.setString(8, user.getPassword());
			sta.setDate(9, new java.sql.Date(user.getLastLoginTime().getTime()));
			sta.setBoolean(10, user.getIsDelete());
			
			sta.setString(11, user.getUserID());
			return (sta.executeUpdate()==1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(conn != null){
				try {
					sta.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return false;
	}

	public List<User> selectPage(User user,int pageNow, int pageNumber,String orderby){
		
		List<User> ret = new ArrayList<User>();
		Connection conn = DataSource.getConnection();
		PreparedStatement pst = DatabaseUtil.getSelectPageStatment(user,conn, pageNow, pageNumber, orderby);
		
		ResultSet rs = null;
		
		try {
			rs= pst.executeQuery();
			while(rs.next()){
				User queryUser = new User();
				queryUser.setUserID(rs.getString("userID"));
				queryUser.setDeliveryStationID(rs.getInt("deliveryStationID"));
				queryUser.setRole(rs.getString("role"));
				queryUser.setDisplayName(rs.getString("displayName"));
				queryUser.setGender(rs.getInt("gender"));
				queryUser.setMobilePhone(rs.getString("mobilePhone"));
				queryUser.setAddress(rs.getString("address"));
				queryUser.setPrepareDelete(rs.getBoolean("prepareDelete"));
				queryUser.setIsDelete(rs.getBoolean("isDelete"));
				queryUser.setPassword(rs.getString("password"));
				queryUser.setLastLoginTime(rs.getDate("lastLoginTime"));
				//ret.setPassword(rs.getString("password"));
				ret.add(queryUser);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pst.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return ret;
	}
	
	/*
	 *  
	 * we need get a collection user which belong to this city.
	 * you need get deliverystationID,and then get User by deliveryStaionID
	 */
	public List<User> selectPageByCityID(Integer cityID,int pageNow,int pageSize,String orderBy){
		
		List<User> ret = new ArrayList<User>();
		Connection conn = DataSource.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		
		if(orderBy == null) 
			orderBy = "userID";
		if(pageNow < 1)
			pageNow = 1;
		if(pageSize < 1)
			pageSize = 10;
		
		try {
			//JOIN
 			pst = conn.prepareStatement("select userID,u.deliveryStationID,role,"
 					+ "displayName,gender,mobilePhone,address,"
 					+ "u.prepareDelete,u.isdelete,password,lastLoginTime "
 					+ "from(select * from User as u, DeliveryStation as d "
 					+ "where u.deliveryStationID=d.deliveryStationID and cityID=?) "
 					+ "order by "+orderBy+" asc limite "+(pageNow-1)*pageSize +","+pageSize +";");	//get operation statement object of database
			pst.setInt(1, cityID);
			rs = pst.executeQuery();		//query database, return ResultSet
			//traverse packaged object
			while(rs.next()) {
					User queryUser = new User();
					queryUser.setUserID(rs.getString("userID"));
					queryUser.setDeliveryStationID(rs.getInt("u.deliveryStationID"));
					queryUser.setRole(rs.getString("role"));
					queryUser.setDisplayName(rs.getString("displayName"));
					queryUser.setGender(rs.getInt("gender"));
					queryUser.setMobilePhone(rs.getString("mobilePhone"));
					queryUser.setAddress(rs.getString("address"));
					queryUser.setPrepareDelete(rs.getBoolean("u.prepareDelete"));
					queryUser.setIsDelete(rs.getBoolean("u.isDelete"));
					queryUser.setPassword(rs.getString("password"));
					queryUser.setLastLoginTime(rs.getDate("lastLoginTime"));
					//ret.setPassword(rs.getString("password"));
					ret.add(queryUser);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			  try {
			 	rs.close();
			 	pst.close();
			 	conn.close();
			 } catch (SQLException e) {
			 	// TODO Auto-generated catch block
		   		e.printStackTrace();
			 }
		}
		return ret;
	}
}